package com.example.mmlist2.storage;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.mmlist2.storage.entity.Account;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.crypto.SecretKey;
import javax.crypto.spec.SecretKeySpec;

public class DatabaseHelper extends SQLiteOpenHelper {

    final static String DATABASENAME = "accounts.db";
    SQLiteDatabase db;
    Context context;

    public DatabaseHelper(Context context) {
        super(context, DATABASENAME, null, 1);
        this.context = context;
        db = this.getWritableDatabase();
    }


    /**
     * 仅且仅在创建数据库时调用一次
     * 自动生成数据库表
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建book表
        String sql = "CREATE TABLE accounts (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "username TEXT NOT NULL," +
                "password TEXT NOT NULL," +
                "title TEXT NOT NULL," +
                "link TEXT," +
                "illustrate TEXT" +
                ");";
        db.execSQL(sql);
        System.out.println("创建数据库成功");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }

    /**
     * 向数据库中添加账户信息
     *
     * @param account 待添加的账户信息
     * @throws Exception 添加账户信息失败时抛出异常
     */
    public void addAccount(Account account, byte[] token) throws Exception {
        ContentValues values = new ContentValues();
        String password = account.getPassword();
        SecretKey secretKey = new SecretKeySpec(token, "AES");
        password = EncryptionUtils.encrypt(password, secretKey);
        values.put("username", account.getUsername());
        values.put("password", password);
        values.put("title", account.getTitle());
        values.put("link", account.getLink());
        values.put("illustrate", account.getIllustrate());
        long id = db.insert("accounts", null, values);
        Log.i("DatabaseHelper", "id: " + id);
    }

    /**
     * 向数据库中添加多个账户信息
     *
     * @param accounts 待添加的账户信息集合
     */
    public int addNewAccounts(List<Map<String, Object>> accounts) throws Exception {
        int count = 0;
        for (Map<String, Object> account : accounts) {
            // 提取变量
            String title = (String) account.get("title");
            String username = (String) account.get("username");
            String password = EncryptionUtils.encryptPassword(context, (String) account.get("password"));
            String illustrate = (String) account.get("note");
            String link = (String) account.get("link");
            if (username == null || password == null || title == null) {
                continue;
            }
            // 判断添加的是否已经在数据库中
            boolean falg = false;
            List<Account> dbAccounts = getAllAccounts();
            for (Account dbAccount : dbAccounts) {
                if (dbAccount.getTitle().equals(title) && dbAccount.getUsername().equals(username)) {
                    falg = true;
                    break;
                }
            }
            if (falg) continue;
            ContentValues values = new ContentValues();
            values.put("title", title);
            values.put("username", username);
            values.put("password", password);
            values.put("link", link);
            values.put("illustrate", illustrate);
            long id = db.insert("accounts", null, values);
            Log.i("DatabaseHelper", "id: " + id);
            count++;
        }
        return count;
    }

    /**
     * 从服务器拉取数据，覆盖本地数据库
     */
    public void newAccount(List<Account> accounts) {
        //清空sqlite数据库
        db.delete("accounts", null, null);

        for (Account account : accounts) {
            ContentValues values = new ContentValues();
            values.put("id", account.getId());
            values.put("username", account.getUsername());
            values.put("password", account.getPassword());
            values.put("title", account.getTitle());
            values.put("link", account.getLink());
            values.put("illustrate", account.getIllustrate());
            db.insert("accounts", null, values);
        }
    }


    /**
     * 从数据库查询部分账号信息
     *
     * @return 包含所有账号信息的List集合
     */
    public List<Account> getAllAccounts() {
        List<Account> accounts = new ArrayList<>();
        Cursor cursor = db.query("accounts", null, null, null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                @SuppressLint("Range") Account account = new Account(
                        cursor.getString(cursor.getColumnIndex("id")),
                        cursor.getString(cursor.getColumnIndex("username")),
                        cursor.getString(cursor.getColumnIndex("title"))
                );
                accounts.add(account);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return accounts;
    }

    /**
     * 从数据库查询所有账号信息
     */
    public List<Account> getAccounts() {
        List<Account> accounts = new ArrayList<>();
        Cursor cursor = db.query("accounts", null, null, null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                @SuppressLint("Range") Account account = new Account(
                        cursor.getString(cursor.getColumnIndex("id")),
                        cursor.getString(cursor.getColumnIndex("username")),
                        cursor.getString(cursor.getColumnIndex("password")),
                        cursor.getString(cursor.getColumnIndex("title")),
                        cursor.getString(cursor.getColumnIndex("link")),
                        cursor.getString(cursor.getColumnIndex("illustrate"))
                );
                accounts.add(account);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return accounts;
    }

    public List<Account> getAllAccount() throws Exception {
        List<Account> accounts = new ArrayList<>();
        Cursor cursor = db.query("accounts", null, null, null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                @SuppressLint("Range") Account account = new Account(
                        cursor.getString(cursor.getColumnIndex("id")),
                        cursor.getString(cursor.getColumnIndex("username")),
                        EncryptionUtils.decryptPassword(context, cursor.getString(cursor.getColumnIndex("password"))),
                        cursor.getString(cursor.getColumnIndex("title")),
                        cursor.getString(cursor.getColumnIndex("link")),
                        cursor.getString(cursor.getColumnIndex("illustrate"))
                );
                accounts.add(account);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return accounts;
    }

    //根据key查询账号信息
    public List<Account> getAccountByKey(String key) {
        List<Account> accounts = new ArrayList<>();
        String searchPattern = "%" + key + "%"; // 构建包含通配符的搜索模式
        try (Cursor cursor = db.rawQuery("select * from accounts where title LIKE ?", new String[]{searchPattern})) {
            if (cursor.moveToFirst()) {
                do {
                    @SuppressLint("Range") Account account = new Account(
                            cursor.getString(cursor.getColumnIndex("id")),
                            cursor.getString(cursor.getColumnIndex("username")),
                            cursor.getString(cursor.getColumnIndex("title"))
                    );
                    accounts.add(account);
                } while (cursor.moveToNext());
            }
        }
        return accounts;
    }


    /**
     * 根据id查询账号信息
     *
     * @param id 账号id
     * @return 返回与id对应的Account对象，若不存在则返回null
     */
    // 根据id查询账号信息
    public Account getAccountById(int id) {
        try (Cursor cursor = db.rawQuery("select * from accounts where id=?",
                new String[]{String.valueOf(id)})) {
            if (cursor != null && cursor.moveToFirst()) {
                @SuppressLint("Range") Account account = new Account(
                        cursor.getString(cursor.getColumnIndex("username")),
                        cursor.getString(cursor.getColumnIndex("password")),
                        cursor.getString(cursor.getColumnIndex("title")),
                        cursor.getString(cursor.getColumnIndex("link")),
                        cursor.getString(cursor.getColumnIndex("illustrate"))
                );
                return account; // 返回查询到的Account对象
            }
        }
        return null;
    }

    /**
     * 从SQLite数据库中根据id删除账号信息
     *
     * @param id 待删除的账号id
     */
    // 从SQLite根据id删除账号信息
    public int deleteAccountById(int id) {
        return db.delete("accounts", "id=?", new String[]{String.valueOf(id)});
    }


    /**
     * 从SQLite数据库中根据id更新账号信息
     *
     * @param id         账号id
     * @param title      账号标题
     * @param username   账号用户名
     * @param password   账号密码
     * @param link       账号链接
     * @param illustrate 账号描述
     */
    // 从SQLite根据id更改信息
    public void updateAccountById1(byte[] token, int id, String title, String username, String password, String link, String illustrate) throws Exception {
        SecretKey secretKey = new SecretKeySpec(token, "AES");
        password = EncryptionUtils.encrypt(password, secretKey);
        ContentValues values = new ContentValues();
        values.put("username", username);
        values.put("password", password);
        values.put("title", title);
        values.put("link", link);
        values.put("illustrate", illustrate);
        db.update("accounts", values, "id=?", new String[]{String.valueOf(id)});
    }

    // 从SQLite根据id更改信息(不改密码的话调用这个，在那边判断密码是否和数据库的相同)
    public void updateAccountById2(int id, String title, String username, String link, String illustrate) {
        ContentValues values = new ContentValues();
        values.put("username", username);
        values.put("title", title);
        values.put("link", link);
        values.put("illustrate", illustrate);
        db.update("accounts", values, "id=?", new String[]{String.valueOf(id)});
    }

    public void updatePasswordById3(byte[] token, int id, String password) throws Exception {
        ContentValues values = new ContentValues();
        SecretKey secretKey = new SecretKeySpec(token, "AES");
        password = EncryptionUtils.encrypt(password, secretKey);
        values.put("password", password);
        db.update("accounts", values, "id=?", new String[]{String.valueOf(id)});
    }


    @Override
    public void close() {
        super.close();
        db.close();
    }


}
